package com.easyattendance.servlets;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.easyattendance.model.Class;
import com.easyattendance.model.Section;
import com.easyattendance.model.Subject;
import com.easyattendance.model.Teacher;
import com.easyattendance.model.TimeTable;
import com.easyattendance.services.AddAllTimeSlotsService;
import com.easyattendance.services.AddClassService;
import com.easyattendance.services.AddSectionService;
import com.easyattendance.services.AddSubjectService;
import com.easyattendance.services.AddTeacherService;
import com.easyattendance.services.TimeTableService;


@WebServlet(displayName="ReadExcelTTServlet", urlPatterns="/ReadExcelTTServlet")
public class ReadExcelTTServlet  extends HttpServlet { 
	
	
	protected void processRequest(HttpServletRequest request, HttpServletResponse response)
	            throws ServletException, IOException {
		PrintWriter out = response.getWriter();
		
		TimeTableService timeTableService = new TimeTableService();
		AddTeacherService teacherService = new AddTeacherService();
		
		AddAllTimeSlotsService allTimeSlotsService = new AddAllTimeSlotsService();
		
		String currentUser = (String) request.getSession().getAttribute("currentUser");
		String sectionEmailId = teacherService.getSectionEmailIdByTeacherId(currentUser);
		String sectionId = teacherService.getSectionIdByTeacherId(currentUser);
		
		//Blank workbook
		XSSFWorkbook workbook = new XSSFWorkbook(); 
		
		//Create a blank sheet with days and timeslot headers...
		XSSFSheet sheet = workbook.createSheet("TimeTable");
		
		int rownum = 0;
		int cellnum = 0;
		Row row = sheet.createRow(rownum++);
		Cell cell = row.createCell(cellnum++);
			
		String[] days = timeTableService.getDaysHeader();
			
			// ----------------------------------------DONOT DELETE THIS-----------------------------------------------------------------
			/*
			 * Read TimeTable inputed by class teacher in our generated excel sheet
			 * 
			 * 
			 */
			
			try
			{
				FileInputStream file = new FileInputStream(new File("F://timetabledemo.xlsx"));

				String slot = null;
				String day = null;
				String subjectId = null;
				String[] cellValues = new String[2];
				String delimiter = ",";
				String teacherId=null;
								
				//Create Workbook instance holding reference to .xlsx file
				workbook = new XSSFWorkbook(file);

				//Get first/desired sheet from the workbook
				sheet = workbook.getSheetAt(0);

				//Iterate through each rows one by one
				Iterator<Row> rowIterator = sheet.iterator();
				
				// to not read days header
				rowIterator.next();
				
				while (rowIterator.hasNext()) 
				{
					row = rowIterator.next();
					//For each row, iterate through all the columns
					Iterator<Cell> cellIterator = row.cellIterator();
					
					for (int i=0; cellIterator.hasNext(); i++) 
					{
						cell = cellIterator.next();
						if(i==0) {
							slot = cell.getStringCellValue();
						}
						else {
							day = days[cell.getColumnIndex()-1];
							cellValues = cell.getStringCellValue().split(delimiter);
							if(cellValues.length==2) {
								subjectId = cellValues[0];
								teacherId = cellValues[1];
								System.out.println("slot:"+slot);
								System.out.println("day:"+day);
								System.out.println("cellvalues:"+subjectId+" "+teacherId);
								String output = addTimeTableEntry(currentUser, day, slot, sectionId, sectionEmailId, subjectId, teacherId);
								System.out.println("============\n"+output+"\n=======");
								out.println(output);
								out.close();
							}
							
						}
					}
					//out = out.concat("\n");
				}
				file.close();
				System.out.println("data extracted from excel");
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
			
			// ------------------------------------------------------------------------------------------------------------
			
			// =====================================
			/*String stud_name = request.getParameter("stud_name");
			long stud_no = Long.parseLong(request.getParameter("stud_no"));
			 
			StudentService studentService = new StudentService();
			
			//First insert user entered data in mysql table through hibernate (IF WE DONT WANT THIS WE SIMPLY DELETE THIS STEP
			Student student = new Student(stud_name,stud_no);
			studentService.register(student);
			
			//Then retrieve all data from mysql and write it to excel sheet 
			List<Student> studentList = new ArrayList<Student>();
			studentList = studentService.getListOfStudents();
			
			Iterator<Student> itr = studentList.iterator();
			
			               
			//This data needs to be written (Object[])
			Map<String, Object[]> data=null;
			data  = new TreeMap<String, Object[]>();
			data.put("0", new Object[] {"ID", "NAME", "PHONE NUMBER"});
			
			while (itr.hasNext()) {
				Student tempStud = itr.next();
				data.put(tempStud.getName(), new Object[] {tempStud.getId(),tempStud.getName() , tempStud.getPhoneNo()});
			}
			 
			//Iterate over data and write to sheet
			Set<String> keyset = data.keySet();
			
			for (String key : keyset)
			{
			    
			    Object [] objArr = data.get(key);
			    int cellnum = 0;
			    for (Object obj : objArr)
			    {
			       Cell cell = row.createCell(cellnum++);
			       if(obj instanceof String)
			            cell.setCellValue((String)obj);
			        else if(obj instanceof Integer)
			            cell.setCellValue((Integer)obj);
			        else if(obj instanceof Long)
			            cell.setCellValue((Long)obj);
			    }
			}
			try 
			{
				//Write the workbook in file system
			    FileOutputStream out = new FileOutputStream(new File("F://timetabledemo.xlsx"));
			    workbook.write(out);
			    out.close();
			    
			    System.out.println("timetabledemo.xlsx written successfully on disk.");
			     
			} 
			catch (Exception e) 
			{
			    e.printStackTrace();
			}*/
			
			// =====================================
		}
	
		protected String addTimeTableEntry(String currentUser, String day, String timeSlot, String sectionId, String sectionEmail, String subjectId, String teacherId) {
			
			TimeTableService timeTableService = new TimeTableService();
			AddSectionService addSectionService = new AddSectionService();
			AddSubjectService addSubjectService = new AddSubjectService();
			AddTeacherService addTeacherService = new AddTeacherService();
			AddClassService addClassService = new AddClassService();
			
			String out = new String();
			
			TimeTable newTimeTable = new TimeTable();
			
			 boolean res = false;
		        
		        // Add section foreign key to timetable
		        boolean isSectionExists = addSectionService.ifAlreadyExists(sectionEmail);
		        Teacher teacher = addTeacherService.getTeacherById(teacherId);
		        Class classObj =addClassService.getClassById(addClassService.getClassIdByTeacherId(currentUser));
		        
		        // Add teacher foreign key to timetable
		        boolean isTeacherExists = addTeacherService.ifAlreadyExists(teacher);
		        
		        // Add semester/year foreign key to timetable
		        boolean isClassExists = addClassService.ifAlreadyExists(classObj);
		        Subject newSubject = new Subject();
		        boolean result = false;
		        
		        if (isSectionExists) {
		        	if(isTeacherExists) {
		        		if(isClassExists) {
		        			newSubject = addSubjectService.getSubjectBySubjectId(subjectId);
		                	boolean isSubjectExists = addSubjectService.ifAlreadyExists(newSubject);
		                	if(isSubjectExists) {
		                		Section section = addSectionService.getSectionBySectionEmail(sectionEmail);
		                		
		                		//Parse timeslot to Date
		                		
		                		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		        		         Date tempSlot=null;
		        				 try {
		        					tempSlot = format.parse("2013-10-10 "+timeSlot+":00");
		        				} catch (ParseException e) {
		        					// TODO Auto-generated catch block
		        					e.printStackTrace();
		        				}
		                        newTimeTable = new TimeTable(day, tempSlot, teacherId, newSubject, teacher, section, classObj);
		                        newTimeTable.setSubject_id(newSubject.getSubject_id());
		                        if(! timeTableService.ifAlreadyExists(newTimeTable)) {
		                        	result = timeTableService.addTimeTable(newTimeTable);
		                        }
		                        else {
		                        	out=("time table already exists");
		                        }
		                	}
		                	else {
		                		out=("subject does not exists");
		                	}
		            	}
		        		else {
		        			out=("semester/year does not exist.. check your login");
		        		}
		        	}
		            	else {
		            		out=("teacher does not exist.. check your login");
		            	}
		        }
		        else {
		        	out=("section does not exist.. check your login");
		        	res = false;
		        }
		        
		        try {
		        	out= new String();
					out.concat("<html>");
					out.concat("<head>");
					out.concat("<title>Registration Successful</title>");
					out.concat("</head>");
					out.concat("<body>");
					out.concat("<center>");
					if (result) {
						out.concat("<h1>TimeTable added Successful</h1>");
						System.out.println("TT entry added to timetable in mysql");
					} else {
						out.concat("<h1>TimeTable add Unsuccessful</h1>");
						out.concat("To try again<a href.concat\"views/teacher/setTimeTable.jsp\">Click here</a>");
					}
					out.concat("</center>");
					out.concat("</body>");
					out.concat("</html>");
			 } finally {		
				 //out.close();
			 }
		     return out;
		}
		
		protected boolean downloadBlankExcelFormat(String sectionId, String sectionEmailId, TimeTableService timeTableService, AddAllTimeSlotsService allTimeSlotsService) {
			/*
			 * Create a blank TimeTable Excel sheet for classTeacher as per the format required...
			 * 
			 * 
			 */
				//Blank workbook
				XSSFWorkbook workbook = new XSSFWorkbook(); 
				
				//Create a blank sheet with days and timeslot headers...
				XSSFSheet sheet = workbook.createSheet("TimeTable");
				
				int rownum = 0;
				int cellnum = 0;
				Row row = sheet.createRow(rownum++);
				Cell cell = row.createCell(cellnum++);
				
				String[] days = timeTableService.getDaysHeader();
				//new..
				cell.setCellValue(sectionId);
				
				for(int i=0; i<days.length; i++) {
					cell = row.createCell(cellnum++);
					cell.setCellValue(days[i]);
				}
				
				String[] timeSlots = allTimeSlotsService.listAllTimeSlotsBySection(sectionEmailId);
				String[] recessTimes = allTimeSlotsService.listAllRecessTimeSlots(sectionId);
				
				for(int i=0; i<timeSlots.length-1; i++) {
					cellnum=0;
					row = sheet.createRow(rownum++);
					cell = row.createCell(cellnum++);
					cell.setCellValue(timeSlots[i]);
					
					for(int j=0; j<recessTimes.length; j++) {
						if(timeSlots[i].equals(recessTimes[j])){
							for(int k=0 ; k<days.length; k++) {
								cell = row.createCell(cellnum++);
								cell.setCellValue("RECESS");
								
							}
							break;
						}
					}
				}
				
				try 
				{
					//Write the workbook in file system
				    FileOutputStream fileOut = new FileOutputStream(new File("F://timetabledemo.xlsx"));
				    workbook.write(fileOut);
				    fileOut.close();
				    
				    System.out.println("timetabledemo.xlsx written successfully on disk.");
				     
				} 
				catch (Exception e) {
				    e.printStackTrace();
				    return false;
				}
				return true;
		}
	
	
		@Override
	    protected void doGet(HttpServletRequest request, HttpServletResponse response)
	            throws ServletException, IOException {
	        processRequest(request, response);
	    }

	    @Override
	    protected void doPost(HttpServletRequest request, HttpServletResponse response)
	            throws ServletException, IOException {
	        processRequest(request, response);
	    }

	    @Override
	    public String getServletInfo() {
	        return "Short description";
	    }// </editor-fold>
	}
